﻿// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace SnapObjects.Data.Oracle
{
    internal abstract class OracleRoutineParameterSchemaProvider : RoutineParameterSchemaProvider
    {
        protected const string SQL_PROCEDURE = @"
SELECT overload as object_uniqueidentifier,
       null as parameter_name, 
       null as parameter_datatype,
       null as parameter_mode,
       null as parameter_precision,
       null as parameter_scale,
       null as parameter_defaultvalue,
       null as parameter_size,
       1 as position, 
       object_id
  FROM {0} procs 
 WHERE {1}";

        private const string SQL_PROCEDURE_PARAMETERS = @"
SELECT arg.overload as object_uniqueidentifier,
       arg.argument_name as parameter_name, 
       arg.data_type as parameter_datatype,
       arg.in_out as parameter_mode,
       arg.data_precision as parameter_precision,
       arg.data_scale as parameter_scale,
       arg.default_value as parameter_defaultvalue,
       arg.char_length as parameter_size,
       arg.POSITION as position
  FROM {0} arg
 WHERE arg.data_level = 0 and object_id = {1} and upper(object_name)=upper('{2}')
 ORDER BY arg.overload, arg.POSITION";

        private const string SQL_CURRENT_USER = "select sys_context('userenv', 'current_user'), sys_context('userenv', 'current_schema') from dual";

        protected const string ALL_PROCEDURES_TABLE = "all_procedures";
        protected const string USER_PROCEDURES_TABLE = "user_procedures";

        protected const string ALL_ARGUMENTS_TABLE = "all_arguments";
        protected const string USER_ARGUMENTS_TABLE = "user_arguments";

        public OracleRoutineParameterSchemaProvider(DataContext dataContext) : base(dataContext)
        {
        }

        protected abstract bool IsParseProcedure { get; }

        protected abstract QueryParameter GetQueryParameter(string routineName, ISqlGenerationHelper helper);
        protected abstract QueryParameter GetQueryParameter(string owner, string routineName, ISqlGenerationHelper helper);
        protected abstract QueryParameter GetQueryParameter(string owner, string packageName, string routineName, ISqlGenerationHelper helper);
        protected abstract QueryParameter GetQueryParameter(string packageName, string routineName, DynamicModel package, ISqlGenerationHelper helper);

        protected abstract Task<QueryParameter> GetQueryParameterAsync(string routineName, ISqlGenerationHelper helper, CancellationToken cancellationToken);
        protected abstract Task<QueryParameter> GetQueryParameterAsync(string owner, string routineName, ISqlGenerationHelper helper, CancellationToken cancellationToken);
        protected abstract Task<QueryParameter> GetQueryParameterAsync(
            string owner, string packageName, string routineName, ISqlGenerationHelper helper, CancellationToken cancellationToken);
        protected abstract Task<QueryParameter> GetQueryParameterAsync(
            string packageName, string routineName, DynamicModel package, ISqlGenerationHelper helper, CancellationToken cancellationToken);

        protected override IList<DynamicModel> QueryParameters(RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            var result = this.GetParameters(nameInfo, helper);

            if (result.Count == 0)
            {
                nameInfo = this.GetNameInfoFromSynonyms(nameInfo);
                if (nameInfo != null)
                {
                    result = this.GetParameters(nameInfo, helper);
                }
            }

            return result;
        }

        protected override async Task<IList<DynamicModel>> QueryParametersAsync(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var result = await this.GetParametersAsync(nameInfo, helper, cancellationToken);

            if (result.Count == 0)
            {
                nameInfo = await this.GetNameInfoFromSynonymsAsync(nameInfo, cancellationToken);
                if (nameInfo != null)
                {
                    result = await this.GetParametersAsync(nameInfo, helper, cancellationToken);
                }
            }

            return result;
        }

        protected override int GetSize(DynamicModel parameter)
        {
            var size = parameter.GetValue<int?>("parameter_size");

            return size != null ? size.Value : 0;
        }

        private IList<DynamicModel> GetParameters(RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            var queryCondition = this.GetQueryParameter(nameInfo, helper);

            if (queryCondition.ProcedureInfo.Count < 1)
            {
                return new List<DynamicModel>();
            }
            else
            {
                var name = helper.RemoveDelimitIdentifier(nameInfo.Name);

                var sqlText = String.Format(SQL_PROCEDURE_PARAMETERS, queryCondition.ArgTable, queryCondition.ObjectId, name);

                var result = _dataContext.SqlExecutor.Select<DynamicModel>(sqlText);

                if (result.Count < 1)
                {
                    return new List<DynamicModel> { queryCondition.ProcedureInfo.FirstOrDefault() };
                }

                this.AddNoParameterRoutine(queryCondition.ProcedureInfo, result);

                if (queryCondition.DefinedInPackage)
                {
                    return this.RemoveValidParameter(result);
                }

                return result;
            }
        }

        private async Task<IList<DynamicModel>> GetParametersAsync(RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var queryCondition = await this.GetQueryParameterAsync(nameInfo, helper, cancellationToken);

            if (queryCondition.ProcedureInfo.Count < 1)
            {
                return new List<DynamicModel>();
            }
            else
            {
                var name = helper.RemoveDelimitIdentifier(nameInfo.Name);

                var sqlText = String.Format(SQL_PROCEDURE_PARAMETERS, queryCondition.ArgTable, queryCondition.ObjectId, name);

                var result = await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(sqlText, new object[] { }, cancellationToken);

                if (result.Count < 1)
                {
                    return new List<DynamicModel> { queryCondition.ProcedureInfo.FirstOrDefault() };
                }

                this.AddNoParameterRoutine(queryCondition.ProcedureInfo, result);

                if (queryCondition.DefinedInPackage)
                {
                    return this.RemoveValidParameter(result);
                }

                return result;
            }
        }

        private void AddNoParameterRoutine(IList<DynamicModel> procedureInfo, IList<DynamicModel> parameter)
        {
            var validProcedureInfo = procedureInfo.Where(m => !String.IsNullOrWhiteSpace(m.GetValue<string>(0)));

            foreach (var item in validProcedureInfo)
            {
                if (!parameter.Any(m => item.GetValue<string>(0).Equals(m.GetValue<string>(0))))
                {
                    parameter.Add(item);
                }
            }
        }

        private QueryParameter GetQueryParameter(RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            QueryParameter queryCondition;

            if (!String.IsNullOrWhiteSpace(nameInfo.Database))
            {
                queryCondition = this.GetQueryParameter(nameInfo.Database, nameInfo.Schema, nameInfo.Name, helper);
            }
            else if (!String.IsNullOrWhiteSpace(nameInfo.Schema))
            {
                var checkPackageSqlText = this.GetCheckPackageSql(nameInfo.Schema, helper);

                var package = _dataContext.SqlExecutor.SelectOne<DynamicModel>(checkPackageSqlText);

                if (package != null)
                {
                    var currentSchema = this.GetCurrentSchema();

                    queryCondition = String.IsNullOrWhiteSpace(currentSchema) ?
                        this.GetQueryParameter(nameInfo.Schema, nameInfo.Name, package, helper) :
                        this.GetQueryParameter(currentSchema, nameInfo.Schema, nameInfo.Name, helper);
                }
                else
                {
                    queryCondition = this.GetQueryParameter(nameInfo.Schema, nameInfo.Name, helper);
                }
            }
            else
            {
                var currentSchema = this.GetCurrentSchema();

                queryCondition = String.IsNullOrWhiteSpace(currentSchema) ?
                    this.GetQueryParameter(nameInfo.Name, helper) : this.GetQueryParameter(currentSchema, nameInfo.Name, helper);
            }

            return queryCondition;
        }

        private async Task<QueryParameter> GetQueryParameterAsync(RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            QueryParameter queryParameter;

            if (!String.IsNullOrWhiteSpace(nameInfo.Database))
            {
                queryParameter = await this.GetQueryParameterAsync(nameInfo.Database, nameInfo.Schema, nameInfo.Name, helper, cancellationToken);
            }
            else if (!String.IsNullOrWhiteSpace(nameInfo.Schema))
            {
                var checkPackageSqlText = this.GetCheckPackageSql(nameInfo.Schema, helper);

                var package = await _dataContext.SqlExecutor.SelectOneAsync<DynamicModel>(checkPackageSqlText, new object[] { }, cancellationToken);

                if (package != null)
                {
                    var currentSchema = await this.GetCurrentSchemaAsync(cancellationToken);

                    queryParameter = String.IsNullOrWhiteSpace(currentSchema) ?
                        await this.GetQueryParameterAsync(nameInfo.Schema, nameInfo.Name, package, helper, cancellationToken) :
                        await this.GetQueryParameterAsync(currentSchema, nameInfo.Schema, nameInfo.Name, helper, cancellationToken);
                }
                else
                {
                    queryParameter = await this.GetQueryParameterAsync(nameInfo.Schema, nameInfo.Name, helper, cancellationToken);
                }
            }
            else
            {
                var currentSchema = await this.GetCurrentSchemaAsync(cancellationToken);

                queryParameter = String.IsNullOrWhiteSpace(currentSchema) ?
                    await this.GetQueryParameterAsync(nameInfo.Name, helper, cancellationToken) :
                    await this.GetQueryParameterAsync(currentSchema, nameInfo.Name, helper, cancellationToken);
            }

            return queryParameter;
        }

        private IList<DynamicModel> RemoveValidParameter(IList<DynamicModel> parameters)
        {
            var functions = parameters
                .Where(m => m.GetValue("position") != null && (decimal)m.GetValue("position") == 0)
                .Select(m => m.GetValue(0));

            if (this.IsParseProcedure)
            {
                return parameters.Where(m => !functions.Contains(m.GetValue(0))).ToList();
            }
            else
            {
                return parameters.Where(m => functions.Contains(m.GetValue(0))).ToList();
            }
        }

        private string GetCheckPackageSql(string packageName, ISqlGenerationHelper helper)
        {
            packageName = helper.RemoveDelimitIdentifier(packageName);

            var sql = @"
SELECT '' as owner, object_name
  FROM user_objects 
 WHERE object_type = 'PACKAGE'
   AND upper(object_name) = upper('{0}')
union
SELECT owner, object_name
  FROM all_objects 
 WHERE object_type = 'PACKAGE'
   AND upper(object_name) = upper('{0}')";

            return String.Format(sql, packageName);
        }

        private RoutineNameInfo GetNameInfoFromSynonyms(RoutineNameInfo nameInfo)
        {
            IList<DynamicModel> resut = new List<DynamicModel>();

            if (String.IsNullOrWhiteSpace(nameInfo.Schema))
            {
                var sql = this.GetSynonymSql(nameInfo, true);

                resut = _dataContext.SqlExecutor.Select<DynamicModel>(sql);
            }

            if (resut.Count < 1)
            {
                var sql = this.GetSynonymSql(nameInfo, false);

                resut = _dataContext.SqlExecutor.Select<DynamicModel>(sql);
            }

            if (resut.Count > 0)
            {
                var name = new RoutineNameInfo()
                {
                    Schema = resut[0].GetValue<string>(0),
                    Name = resut[0].GetValue<string>(1),
                };

                name.FullName = $"{name.Schema}.{name.Name}";

                return name;
            }

            return null;
        }

        private async Task<RoutineNameInfo> GetNameInfoFromSynonymsAsync(RoutineNameInfo nameInfo, CancellationToken cancellationToken)
        {
            IList<DynamicModel> resut = new List<DynamicModel>();

            if (String.IsNullOrWhiteSpace(nameInfo.Schema))
            {
                var sql = this.GetSynonymSql(nameInfo, true);

                resut = await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(sql, new object[] { }, cancellationToken);
            }

            if (resut.Count < 1)
            {
                var sql = this.GetSynonymSql(nameInfo, false);

                resut = await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(sql, new object[] { }, cancellationToken);
            }

            if (resut.Count > 0)
            {
                var name = new RoutineNameInfo()
                {
                    Schema = resut[0].GetValue<string>(0),
                    Name = resut[0].GetValue<string>(1),
                };

                name.FullName = $"{name.Schema}.{name.Name}";

                return name;
            }

            return null;
        }

        private string GetSynonymSql(RoutineNameInfo nameInfo, bool isUserTable)
        {
            var whereClause = $"synonym_name=upper('{nameInfo.Name}')";

            if (isUserTable)
            {
                return $"select table_owner, table_name from user_synonyms where {whereClause}";
            }
            else
            {
                if (!String.IsNullOrWhiteSpace(nameInfo.Schema))
                {
                    whereClause = $"{whereClause} and owner=upper('{nameInfo.Schema}')";
                }

                return $"select table_owner, table_name from all_synonyms where {whereClause}";
            }
        }

        private string GetCurrentSchema()
        {
            if (_dataContext.SqlExecutor is SqlExecutor sqlExecutor)
            {
                var ret = sqlExecutor.Select<DynamicModel>(SQL_CURRENT_USER, false);

                if (ret.Count > 0)
                {
                    var currentUser = ret[0].GetValue<string>(0);
                    var currentSchema = ret[0].GetValue<string>(1);

                    if (!currentUser.Equals(currentSchema))
                    {
                        return currentSchema;
                    }
                }
            }

            return String.Empty;
        }

        private async Task<string> GetCurrentSchemaAsync(CancellationToken cancellationToken)
        {
            if (_dataContext.SqlExecutor is SqlExecutor sqlExecutor)
            {
                var ret = await sqlExecutor.SelectAsync<DynamicModel>(SQL_CURRENT_USER, false, new object[] { }, cancellationToken);

                if (ret.Count > 0)
                {
                    var currentUser = ret[0].GetValue<string>(0);
                    var currentSchema = ret[0].GetValue<string>(1);

                    if (!currentUser.Equals(currentSchema))
                    {
                        return currentSchema;
                    }
                }
            }

            return String.Empty;
        }

        protected class QueryParameter
        {
            public QueryParameter(string argTable, bool definedInPackage, IList<DynamicModel> procedureInfo)
            {
                this.ArgTable = argTable;
                this.ProcedureInfo = procedureInfo;
                this.DefinedInPackage = definedInPackage;

                if (this.ProcedureInfo.Count > 0)
                {
                    var tmpProcedureInfo = this.ProcedureInfo.FirstOrDefault();
                    
                    this.ObjectId = tmpProcedureInfo.GetValue<int>(9);
                }
            }

            public int ObjectId { get; }
            public string ArgTable { get; }
            public bool DefinedInPackage { get; }
            public IList<DynamicModel> ProcedureInfo { get; }
        }
    }
}
